package com.Alan.dao.order;

import com.Alan.dao.BaseDao;
import com.Alan.pojo.Order;
import com.Alan.pojo.Supplier;
import com.mysql.cj.util.StringUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao{
    @Override
    public int getOrderCount(Connection connection, String ordName, String isPay, int supId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) ordCount from `order` o, supplier s where o.supId = s.id");
            List<Object> list = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(ordName)){
                sql.append(" and o.ordName like ?");
                list.add("%" + ordName + "%");
            }
            if (!StringUtils.isNullOrEmpty(isPay)){
                sql.append(" and o.isPay = ?");
                list.add(isPay);
            }
            if (supId > 0){
                sql.append(" and o.supId = ?");
                list.add(supId);
            }

            Object[] params = list.toArray();
            System.out.println("提示根据条件查询Supplier总数SQL=" + sql.toString());

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
                if (resultSet.next()){
                    count = resultSet.getInt("ordCount");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:OrderDaoImpl -> getOrderCount");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return count;
    }

    @Override
    public List<Order> getOrderList(Connection connection, String ordName, String isPay, int supId, int startRow, int pageRows) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Order> listOrd = new ArrayList<Order>();

        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select o.*, s.supCode supCode, s.supName supName from `order` o, supplier s where o.supId = s.id");
            List<Object> list = new ArrayList<>();

            if (!StringUtils.isNullOrEmpty(ordName)){
                sql.append(" and o.ordName like ?");
                list.add("%" + ordName + "%");
            }
            if (!StringUtils.isNullOrEmpty(isPay)){
                sql.append(" and o.isPay = ?");
                list.add(isPay);
            }
            if (supId > 0){
                sql.append(" and o.supId = ?");
                list.add(supId);
            }
            list.add(startRow);
            list.add(pageRows);
            sql.append(" order by id limit ?, ?");
            Object[] params = list.toArray();
            System.out.println("获取查询的供应商列表的SQL语句 === " + sql.toString());


            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
                while (resultSet.next()){
                    Order order = new Order();
                    order.setId(resultSet.getInt("id"));
                    order.setOrdCode(resultSet.getString("ordCode"));
                    order.setOrdName(resultSet.getString("ordName"));
                    order.setDescribe(resultSet.getString("describe"));
                    order.setCount(resultSet.getBigDecimal("count"));
                    order.setUnit(resultSet.getString("unit"));
                    order.setTotalPrice(resultSet.getBigDecimal("totalPrice"));
                    order.setSupId(resultSet.getInt("supId"));
                    order.setSupCode(resultSet.getString("supCode"));
                    order.setSupName(resultSet.getString("supName"));
                    order.setIsPay(resultSet.getString("isPay"));
                    listOrd.add(order);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:OrderDaoImpl -> getOrderList");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return listOrd;
    }

    @Override
    public int orderExist(Connection connection, String ordCode) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int existRows = 0;

        if (connection != null){
            String sql = "select count(1) existRows from `order` where ordCode = ?;";
            Object[] params = {ordCode};

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
                if (resultSet.next()){
                    existRows = resultSet.getInt("existRows");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:OrderDaoImpl -> orderExist");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return existRows;
    }

    @Override
    public Order getOrderInformation(Connection connection, int ordId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Order order = null;

        if (connection != null){
            String sql = "select o.*, s.supCode supCode, s.supName supName from `order` o, supplier s where o.supId = s.id and o.id = ?";
            Object[] params = {ordId};

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
                if (resultSet.next()){
                    order = new Order();
                    order.setId(resultSet.getInt("id"));
                    order.setOrdCode(resultSet.getString("ordCode"));
                    order.setOrdName(resultSet.getString("ordName"));
                    order.setDescribe(resultSet.getString("describe"));
                    order.setCount(resultSet.getBigDecimal("count"));
                    order.setUnit(resultSet.getString("unit"));
                    order.setTotalPrice(resultSet.getBigDecimal("totalPrice"));
                    order.setSupCode(resultSet.getString("supCode"));
                    order.setSupName(resultSet.getString("supName"));
                    order.setIsPay(resultSet.getString("isPay"));
                    order.setCreatedByUserCode(resultSet.getString("createdByUserCode"));
                    order.setCreatedByUserName(resultSet.getString("createdByUserName"));
                    order.setCreationDate(resultSet.getDate("creationDate"));
                    order.setModifyByUserCode(resultSet.getString("modifyByUserCode"));
                    order.setModifyByUserName(resultSet.getString("modifyByUserName"));
                    order.setModifyDate(resultSet.getDate("modifyDate"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:OrderDaoImpl -> getOrderInformation");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return order;
    }

    @Override
    public int updateOrder(Connection connection, Order order) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "update `order` " +
                    "set ordName = ?, `describe` = ?, count = ?, unit = ?, totalPrice = ?, " +
                    "isPay = ?, modifyByUserCode = ?, modifyByUserName = ?, modifyDate = ?" +
                    "where id = ?;";
            Object[] params = {
                    order.getOrdName(), order.getDescribe(), order.getCount(), order.getUnit(), order.getTotalPrice(),
                    order.getIsPay(), order.getModifyByUserCode(),
                    order.getModifyByUserName(), order.getModifyDate(), order.getId()
            };

            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:OrderDaoImpl -> updateOrder");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }

        return updateRows;
    }

    @Override
    public int addOrder(Connection connection, Order order) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "insert into `order` " +
                    "(ordCode, ordName, `describe`, count, unit, totalPrice, " +
                    "supId, isPay, createdByUserCode, createdByUserName, creationDate) " +
                    "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            Object[] params = {
                    order.getOrdCode(), order.getOrdName(), order.getDescribe(), order.getCount(),
                    order.getUnit(), order.getTotalPrice(), order.getSupId(), order.getIsPay(),
                    order.getModifyByUserCode(), order.getModifyByUserName(), order.getModifyDate()
            };

            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:OrderDaoImpl -> updateOrder");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }

        return updateRows;
    }

    @Test
    public void test(){
        OrderDao orderDao = new OrderDaoImpl();

        /*int orderCount = orderDao.getOrderCount(BaseDao.getConnection(), "", "已支付", 0);
        System.out.println(orderCount);*/

        /*List<Order> orderList = orderDao.getOrderList(BaseDao.getConnection(), "", "", "", 0, 4);
        for (Order order : orderList) {
            System.out.println(order.toString());
        }*/

        /*int bill2016_002 = orderDao.orderExist(BaseDao.getConnection(), "BILL201_002");
        System.out.println(bill2016_002);*/

        /*System.out.println(orderDao.getOrderInformation(BaseDao.getConnection(), 2).toString());*/
    }
}
